﻿using System.ComponentModel;
using System.Data.OleDb;
using Entity;

namespace DAO
{
    public class KhuVucDAO
    {
        static string connectionString = new connection().getConnectionString();

        
        public BindingList<KhuVuc> LoadAll()
        {
            
            BindingList<KhuVuc> list = new BindingList<KhuVuc>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select * from KhuVuc;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                int ma = (int)reader["MaKhuVuc"];
                string ten = (string)reader["TenKhuVuc"];
                KhuVuc kv = new KhuVuc();
                kv.MaKhuVuc = ma;
                kv.TenKhuVuc = ten;
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list;
        }

        public KhuVuc LoadKhuVucByID(int id)
        {

            BindingList<KhuVuc> list = new BindingList<KhuVuc>();
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "select TOP 1 * from KhuVuc WHERE MaKhuVuc = @MaKhuVuc;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaKhuVuc", OleDbType.VarChar).Value = id;
            OleDbDataReader reader = command.ExecuteReader();
            //B4: Xu ly ket qua tra ve
            while (reader.Read())
            {
                int ma = (int)reader["MaKhuVuc"];
                string ten = (string)reader["TenKhuVuc"];
                KhuVuc kv = new KhuVuc();
                kv.MaKhuVuc = ma;
                kv.TenKhuVuc = ten;
                list.Add(kv);
            }

            reader.Close();
            //B5: dong ket noi
            connection.Close();
            return list[0];
        }


        public int DeleteByStringId(int id)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "DELETE FROM KhuVuc WHERE MaKhuVuc = @MaKhuVuc;";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@MaKhuVuc", OleDbType.VarChar).Value = id;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();

            return result;
        }
        public int Insert(KhuVuc kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "INSERT INTO KhuVuc (TenKhuVuc) VALUES(@TenKhuVuc);";
            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenKhuVuc", OleDbType.VarChar).Value = kv.TenKhuVuc;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }
        public int Update(KhuVuc kv)
        {
            //B1: mo ket noi DB
            OleDbConnection connection = new OleDbConnection(connectionString);
            connection.Open();
            //B2: cbi cau truy van
            string sql = "UPDATE KhuVuc SET TenKhuVuc = @TenKhuVuc WHERE MaKhuVuc = @MaKhuVuc;";

            //B3: Thuc thi cau truy van
            OleDbCommand command = new OleDbCommand(sql, connection);
            command.Parameters.Add("@TenKhuVuc", OleDbType.VarChar).Value = kv.TenKhuVuc;
            command.Parameters.Add("@MaKhuVuc", OleDbType.VarChar).Value = kv.MaKhuVuc;
            int result = command.ExecuteNonQuery();
            //B5: dong ket noi
            connection.Close();
            return result;
        }

    }
}
